Analysis of the Difference between the Values of ${} and {} in mybatis

As a lightweight ORM framework, mybatis is widely used, and its hands-on use is relatively simple. A mature framework must have exquisite design, which is worth learning.

When using the mybatis framework, there are two ways to get the passed-in parameters in the sql statement:

${paramName} #{paramName}

How to understand these two ways of transmitting parameters? Take you closer to the meaning behind it as follows.

Let's review the native Jdbc query:

public static void main(String[] args) throws Exception {

  // sql Statement 
  String sql = "select id,name from customer limit 2";
  // 1. Load driver ,  Used here mysql The driver package is 8.0 Version ,  If yes 5.0+ Version ,  Please modify the following class path 
  Class.forName("com.mysql.cj.jdbc.Driver");
  // 2. Get a database connection 
  String url = "jdbc:mysql://localhost:3306/work?useSSL=false&useUnicode=true" +
    "&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true" +
    "&useLegacyDatetimeCode=false&serverTimezone=UTC";
  Connection conn = DriverManager.getConnection(url,"root", "123456");
  // 3 That can be executed sql Object of the statement 
  Statement st = conn.createStatement();
  // 4 Using objects to execute SQL Statement 
  ResultSet rs = st.executeQuery(sql);
  // 5 , processing sql The result set returned by the statement 
  while(rs.next()){
    //  Obtain 1 Row data 
    Integer id = rs.getInt("id");
    String name = rs.getString("name");
    System.out.println("sql Query : id = " + id + " , name = " + name);
  }
  // 6 Release resources 
  rs.close();
  st.close();
  conn.close();
}

Console printing:

sql query: id = 1, name = Li Bai
sql query: id = 2, name = Du Fu

Those who know Jdbc will know that the two statements in steps 3 and 4 can also be replaced by the following two statements:

// 3. Create  PreparedStatement  Object to execute sql
PreparedStatement preparedStatement = conn.prepareStatement(sql);
// 4. Execute sql Statement 
ResultSet rs = preparedStatement.executeQuery();

Let's compare the differences:

The sql statement is passed in when the PreparedStatement object is created, and sql is not passed in when the statement is executed; Statement is just the opposite

This leads to the concept of precompilation:

If you use PreparedStatement object, when you execute step 3, since you have passed in sql, it is equivalent to this sql being compiled by the database (the compilation of sql statement by the database is also quite complicated), so you don't need to pass in sql when you execute step 4, because the database already knows the sql you want to execute, and you only need to pass in parameters; If you use the Statement object, it is easy to understand that the database did not parse your sql in advance, because you created the object without passing it in; When sql is executed, the database is recompiled and executed.

Seeing this, I may only remember one pre-compiled sql, one non-pre-compiled, and I don't know the difference in actual development, which will be illustrated below.

Is PreparedStatement object better than Statement object?

There is no such absolute thing, everyone should understand:

The advantage of PreparedStatement object is that sql has been compiled in advance, and the rest of the work is to pass in parameters. The compiled sql can be reused. If different parameters are passed in, the database will fill the corresponding parameters into the compiled sql. The Statement object is passed into sql every time and thrown to the database to compile and then execute; However, the overhead of creating PreparedStatement objects is larger than that of Statement objects.

Returning to daily development, we don't have to pay attention to the above differences at all. In fact, we use PreparedStatement objects in 910% of scenarios, which may not be perceived at ordinary times, because this is the encapsulation of the framework. Furthermore, when the system has performance problems, it is definitely not because of these two objects.

The above briefly reviews the PreparedStatement and Statement objects in the lower Jdbc;

It can be expected that the two values of ${} and # {} in mybatis are equivalent to the difference between PreparedStatement and Statement objects.

# {} Pass parameters, which means that sql has been precompiled, and the parameters you pass in are really just parameters! ${} Pass the reference, pass it on as you please, and I'll compile it after passing it on

What's the difference in use? Understand the following two scenarios:

1. Look at the following service and sql statements

@Override
public List<Map<String, Object>> listUser() {
  String param = " and name = ' Li Bai '";
  return indexMapper.listUser(param);
}
<select id="listUser" resultType="map">
  select * from customer
  where 1 = 1 #{param}
</select>

Can the above code be queried normally?

### Error querying database. Cause: java.sql.SQLSyntaxErrorException: You have
an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near ''and name =\ 'Li Bai\'''

Can't! Will report sql statement rule error, said before, # {} value means sql has been compiled, you pass in only parameters

Corresponding to the above example:

sql means: select * from customer where 1 = 1

Parameter is: and name = 'Li Bai'

At this point sql can run correctly, but not with the parameters passed in. To understand that what you passed in is really just parameters, don't mix with the previous sql.

But this is obviously wrong, because what I want to say is actually this:

sql means: select * from customer where 1 = 1 and name =?

Parameter refers to: 'Li Bai'

At this time, replacing parameters with placeholders can run the whole statement normally.

So you should use ${param} at this time, because using ${} does not compile what belongs to sql in advance.

This example shows that when you pass in parameters are not only parameters, but actually a small piece of sql. If you want to splice with the original sql at 1, you have to pass the parameters with ${}, which is equivalent to throwing them after splicing.

Database to parse the whole statement;

The question mark in sql represents the parameter placeholder, which is also one of the characteristics of PreparedStatement objects. It will replace the parameter 11 you passed in with the placeholder

On the contrary, it is as follows:

@Override
public List<Map<String, Object>> listUser() {
  String param = " Li Bai ";
  return indexMapper.listUser(param);
}
<select id="listUser" resultType="map">
  select * from customer
  where 1 = 1 and name = #{param}
</select>

In this case, it is correct to use # {}, because the parameters passed in are just parameters, which can be replaced in the sql statement.

2. Impact on parameter types

@Override
public List<Map<String, Object>> listUser() {
  String param = " Li Bai ";
  return indexMapper.listUser(param);
}
<select id="listUser" resultType="map">
  select * from customer
  where 1 = 1 and name = ${param}
</select>

Can the above code be executed successfully?

It stands to reason that only parameters are passed in, which should be executed regardless of whether they are precompiled or not, but an error will still be reported.

This is the sql statement printed at execution time:

select * from customer where 1 = 1 and name =  Li Bai 

Obviously, the problem is that the parameters are not quoted in single quotes. The name field is of string type, and the passed-in string is also a string, but the mybatis conversion is not quoted in single quotes.

So when you pass in a string type parameter, you should take the value with # {}, which is automatically enclosed in single quotation marks.

Look at the following statement again:

@Override
public List<Map<String, Object>> listUser() {
  String param = "name";
  return indexMapper.listUser(param);
}
<select id="listUser" resultType="map">
  select * from customer
  where 1 = 1
  order by ${param} desc
</select>

The parameter passed in at this time is the name of the field to be sorted. As mentioned before, if # {} is used, single quotation marks will be automatically added, but do the sorted fields after order by need single quotation marks?

No, so you can only use the value of ${} in this case.

You may find that using # {} here will not report an error, because mysql supports this writing, but the query result is not correct.

In daily development, as long as you can understand the above two situations, you can use ${} and # {} correctly. Because of the difference in value principles between these two methods, it is easy to understand that # {} can prevent sql injection.

Summarize